﻿Public Class masPrice
    Private PRCID = Nothing
    Private PRDCODE = Nothing
    Private PCKPID = Nothing
    Private ST As Date = Nothing
    Private EN As Date = Nothing
    Private OPD_PRICE = Nothing
    Private IPD_PRICE = Nothing
    Private STATUS = Nothing
    Private selectMasproductWithPriceDate = "SELECT a.`prdcode` AS 'รหัสผลิตภัณฑ์', a.`prdname` AS 'ชื่อผลิตภัณฑ์', a.`prdcat` AS 'รหัสกลุ่มผลิตภัณฑ์', b.`catname` AS 'กลุ่มผลิตภัณฑ์', c.`st` AS 'วันเริ่มต้น', c.`en` AS 'วันสิ้นสุด', a.`last_cost` AS 'ต้นทุนสุดท้าย', a.`avg_cost` AS 'ต้นทุนเฉลี่ย', c.`opd_price` AS 'OPD', c.`ipd_price` AS 'IPD', c.`prcid` AS 'รหัสราคา' FROM masproduct a LEFT JOIN masprdcats b ON a.`prdcat` = b.`prdcat` LEFT JOIN (SELECT * FROM masprice WHERE NOW() BETWEEN st AND en) c ON a.`prdcode` = c.`prdcode`"
    Private selectPckproduct = "SELECT `pckpid` as 'รหัสกลุ่มผลิตภัณฑ์', `pckname` as 'ชื่อกลุ่มผลิตภัณฑ์', `remark` as 'หมายเหตุ' FROM pckproduct"
    'Private selectMasdrgdoswuse = "SELECT USEID, DUCODE, DUSENAME, STATUS FROM masdrgdoswuse"
    'Private selectMasdrgacc = "SELECT DGTYID, DRGTYPE, convert(bit, [STATUS]) FROM MASDRGTYPE"

    Dim connect As CONDB = CONDB.NewConnection()
    'Dim connectlocal As conlocal = conlocal.NewConnection

    Property PRCID_ As Integer
        Get
            Return PRCID
        End Get
        Set(value As Integer)
            PRCID = value
        End Set
    End Property

    Property PRDCODE_ As Integer
        Get
            Return PRDCODE
        End Get
        Set(value As Integer)
            PRDCODE = value
        End Set
    End Property

    Property PCKPID_ As Integer
        Get
            Return PCKPID
        End Get
        Set(value As Integer)
            PCKPID = value
        End Set
    End Property

    Property ST_ As Date
        Get
            Return ST
        End Get
        Set(value As Date)
            ST = value
        End Set
    End Property

    Property EN_ As Date
        Get
            Return EN
        End Get
        Set(value As Date)
            EN = value
        End Set
    End Property

    Property OPD_PRICE_ As Double
        Get
            Return OPD_PRICE
        End Get
        Set(value As Double)
            OPD_PRICE = value
        End Set
    End Property

    Property IPD_PRICE_ As Double
        Get
            Return IPD_PRICE
        End Get
        Set(value As Double)
            IPD_PRICE = value
        End Set
    End Property

    Property STATUS_ As Boolean
        Get
            Return STATUS
        End Get
        Set(value As Boolean)
            If value = True Then
                STATUS = 1
            Else
                STATUS = 0
            End If
        End Set
    End Property

    Property selectMasproductWithPriceDate_ As String
        Get
            Return selectMasproductWithPriceDate
        End Get
        Set(value As String)

        End Set
    End Property

    Property selectPckproduct_ As String
        Get
            Return selectPckproduct
        End Get
        Set(value As String)

        End Set
    End Property

    Public Sub insertMasprice()
        If PRCID Is Nothing Then
            'กรณีไม่เคยตั้งราคามาก่อน
            Try
                connect = CONDB.NewConnection()
                'connectlocal = conlocal.NewConnection()
                'Dim dischargeID As Integer

                Dim startDay As String = ST.Day
                Dim startMonth As String = ST.Month
                Dim startYear As String = ST.Year
                ST = startYear & "-" & startMonth & "-" & startDay
                EN = "9456-09-09"


                connect.ExecuteNonQuery("INSERT INTO masprice (prdcode, st, en, opd_price, ipd_price, status) VALUES ('" & PRDCODE & "', '" & ST.ToString("yyyy-MM-dd") & "', '" & EN.ToString("yyyy-MM-dd") & "', '" & OPD_PRICE & "', '" & IPD_PRICE & "', '" & STATUS & "')")
                'dischargeID = connect.ExecuteScalar("INSERT INTO masdischarge (DISCHARGE, STATUS) VALUES ('" & masdischargeDescription & "', '" & masdischargeStatus & "'); SELECT LAST_INSERT_ID();")
                'connectlocal.ExecuteNonQuery("INSERT INTO MASDISCHARGE (DISCHGID, DISCHARGE, STATUS) VALUES ('" & dischargeID & "', '" & masdischargeDescription & "', '" & masdischargeStatus & "')")
                MsgBox("ท่านได้ทำการเพิ่มข้อมูลราคามาตรฐานเรียบร้อยแล้ว")
            Catch ex As Exception
                MsgBox(vbCrLf & ex.Message)
            End Try
        Else
            'กรณีที่เคยตั้งราคาแล้ว
            Dim lastSTDate As Date
            lastSTDate = connect.GetTable("SELECT a.`prdcode` AS 'รหัสผลิตภัณฑ์', a.`prdname` AS 'ชื่อผลิตภัณฑ์', a.`prdcat` AS 'รหัสกลุ่มผลิตภัณฑ์', b.`catname` AS 'กลุ่มผลิตภัณฑ์', c.`st` AS 'วันที่เริ่มต้นใช้ราคา', c.`en` AS 'วันที่สิ้นสุดการใช้ราคา', c.`opd_price` AS 'ราคาผู้ป่วยนอก', c.`ipd_price` AS 'ราคาผู้ป่วยใน' FROM masproduct a LEFT JOIN masprdcats b ON a.`prdcat` = b.`prdcat` LEFT JOIN (SELECT * FROM masprice WHERE NOW() BETWEEN st AND en) c ON a.`prdcode` = c.`prdcode` WHERE a.`prdcode` = '" & PRDCODE & "'").Rows(0)("วันที่เริ่มต้นใช้ราคา")

            'connect.ExecuteNonQuery("UPDATE masprice SET STATUS = '0' WHERE PRDCODE = '" & PRDCODE & "' AND ST > NOW()")
            connect.ExecuteNonQuery("DELETE FROM masprice WHERE prdcode = '" & PRDCODE & "' AND st > NOW()")

            EN = ST.AddDays(-1)

            connect.ExecuteNonQuery("UPDATE masprice SET en = '" & EN.ToString("yyyy-MM-dd") & "' WHERE prdcode = '" & PRDCODE & "' AND st <= NOW() AND en >= NOW()")

            EN = "9456-09-09"

            connect.ExecuteNonQuery("INSERT INTO masprice (prdcode, st, en, opd_price, ipd_price, status) VALUES ('" & PRDCODE & "', '" & ST.ToString("yyyy-MM-dd") & "', '" & EN.ToString("yyyy-MM-dd") & "', '" & OPD_PRICE & "', '" & IPD_PRICE & "', '" & STATUS & "')")
        End If

        connect.Dispose()
        'connectlocal.Dispose()
    End Sub

    Public Sub insertMaspricePackage()
        If PRCID Is Nothing Then
            Try
                connect = CONDB.NewConnection()
                'connectlocal = conlocal.NewConnection()
                'Dim dischargeID As Integer

                Dim startDay As String = ST.Day
                Dim startMonth As String = ST.Month
                Dim startYear As String = ST.Year + 543
                ST = startYear & "-" & startMonth & "-" & startDay

                EN = "9456-09-09"

                connect.BeginTrans()

                Dim queryString As String
                queryString = "SELECT pcklstid FROM pcklistproduct WHERE pckpid = '" & PCKPID & "'"

                Dim maspriceData As DataTable
                maspriceData = connect.GetTable(queryString)
                'masgrpdrgData = connectlocal.GetTable(objSelectDataMasgrpdrg.selectMasgrpdrg_)

                For i As Integer = 0 To maspriceData.Rows.Count - 1

                Next

                connect.ExecuteNonQuery("INSERT INTO masprice (prdcode, st, en, opd_price, ipd_price, status) VALUES ('" & PRDCODE & "', '" & ST & "', '" & EN & "', '" & OPD_PRICE & "', '" & IPD_PRICE & "', '" & STATUS & "')")
                'dischargeID = connect.ExecuteScalar("INSERT INTO masdischarge (DISCHARGE, STATUS) VALUES ('" & masdischargeDescription & "', '" & masdischargeStatus & "'); SELECT LAST_INSERT_ID();")
                'connectlocal.ExecuteNonQuery("INSERT INTO MASDISCHARGE (DISCHGID, DISCHARGE, STATUS) VALUES ('" & dischargeID & "', '" & masdischargeDescription & "', '" & masdischargeStatus & "')")
                MsgBox("ท่านได้ทำการตั้งราคามาตรฐานเรียบร้อยแล้ว")
            Catch ex As Exception
                connect.RollbackTrans()
                MsgBox(ex.ToString)
            End Try
            connect.Dispose()
        Else

        End If
    End Sub
End Class